package com.liliya.dao.impl;

import com.liliya.dao.AdDao;
import com.liliya.dao.BaseDao;
import com.liliya.pojo.Ad;

import java.sql.Date;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Description 具体的sql实现
 * @Author hhy
 * @Date 2021/1/6 20:32
 */
public class AdDaoImpl extends BaseDao implements AdDao {
    @Override
    public void addAd(Ad ad) {
        String sql = "insert into T_AD (adnum, adname, adlink, adalt, adcomment,ADDATETIME) VALUES (adid.nextval,?,?,?,?,sysdate)";
        try {
            conn = getConn();
            pstm = conn.prepareStatement(sql);
            pstm.setString(1, ad.getAdname());
            pstm.setString(2, ad.getAdlink());
            pstm.setString(3, ad.getAdalt());
            pstm.setString(4, ad.getAdcomment());
            pstm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        close();
        System.out.println(sql);
    }

    @Override
    public Ad findAdByAdNum(Integer adnum) {
        Ad ad = null;
        String sql = "select adnum, adname, adlink, adalt, adcomment, addatetime, adshowtime\n" +
                "from T_AD where ADNUM = ?";
        try {
            conn = getConn();
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1, adnum);
            rs = pstm.executeQuery();
            while (rs.next()) {
                ad = new Ad();
                ad.setAdnum(rs.getInt("adnum"));
                ad.setAdname(rs.getString("adname"));
                ad.setAdlink(rs.getString("adlink"));
                ad.setAdalt(rs.getString("adalt"));
                ad.setAdcomment(rs.getString("adcomment"));
                ad.setAddatetime(rs.getDate("addatetime"));
                ad.setAdshowtime(rs.getInt("adshowtime"));
            }
            updateAdShowTime(adnum);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        close();
        System.out.println(sql);
        return ad;
    }

    //这里需要一个更新显示次数的sql ,传入一个广告num
    private void updateAdShowTime(Integer adnum) {
        String sql = "update T_AD\n" +
                "set ADSHOWTIME = ADSHOWTIME+1\n" +
                "where ADNUM = ?";
        try {
            conn = getConn();
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1,adnum);
            pstm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        close();
        System.out.println(sql);
    }

    @Override
    public List<Ad> findAds(Integer start, Integer end) {
        List<Ad> ads = new ArrayList<>();
        String sql = "select adnum, adname, adlink, adalt, adcomment, addatetime, adshowtime\n" +
                "from (select adnum, adname, adlink, adalt, adcomment, addatetime, adshowtime,ROWNUM r from T_AD) where r between ? and ?";
        try {
            conn = getConn();
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1, start);
            pstm.setInt(2, end);
            rs = pstm.executeQuery();
            while (rs.next()) {
                Ad ad = new Ad();
                ad.setAdnum(rs.getInt("adnum"));
                ad.setAdname(rs.getString("adname"));
                ad.setAdlink(rs.getString("adlink"));
                ad.setAdalt(rs.getString("adalt"));
                ad.setAdcomment(rs.getString("adcomment"));
                ad.setAddatetime(rs.getDate("addatetime"));
                ad.setAdshowtime(rs.getInt("adshowtime"));
                ads.add(ad);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        close();
        System.out.println(sql);
        return ads;
    }

    @Override
    public Ad findAd(Integer row) {
        Ad ad = null;
        String sql = "select adnum, adname, adlink, adalt, adcomment, addatetime, adshowtime\n" +
                "from (select adnum, adname, adlink, adalt, adcomment, addatetime, adshowtime,ROWNUM r from T_AD) where r = ?";
        try {
            conn = getConn();
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1, row);
            rs = pstm.executeQuery();
            while (rs.next()) {
                ad = new Ad();
                ad.setAdnum(rs.getInt("adnum"));
                ad.setAdname(rs.getString("adname"));
                ad.setAdlink(rs.getString("adlink"));
                ad.setAdalt(rs.getString("adalt"));
                ad.setAdcomment(rs.getString("adcomment"));
                ad.setAddatetime(rs.getDate("addatetime"));
                ad.setAdshowtime(rs.getInt("adshowtime"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        close();
        System.out.println(sql);
        return ad;
    }

    @Override
    public Integer adTotal() {
        Integer total = null;
        String sql = "select count(1) c from T_AD";
        try {
            conn = getConn();
            pstm = conn.prepareStatement(sql);
            rs = pstm.executeQuery();
            while (rs.next()) {
                total = rs.getInt("c");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        close();
        System.out.println(sql);
        return total;
    }

    @Override
    public void deleteAd(Integer adnum) {
        String sql = "delete T_AD where ADNUM = ?";
        try {
            conn = getConn();
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1, adnum);
            pstm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        close();
        System.out.println(sql);
    }

    @Override
    public void updateAd(Ad ad) {
        String sql = "update T_AD\n" +
                "set adname = ?,\n" +
                "    adlink = ?,\n" +
                "    adalt = ?,\n" +
                "    adcomment = ?,\n" +
                "    addatetime = ?,\n" +
                "    adshowtime = ?\n" +
                "where ADNUM = ?";
        try {
            conn = getConn();
            pstm = conn.prepareStatement(sql);
            pstm.setString(1, ad.getAdname());
            pstm.setString(2, ad.getAdlink());
            pstm.setString(3, ad.getAdalt());
            pstm.setString(4, ad.getAdcomment());
            pstm.setDate(5, (Date) ad.getAddatetime());
            pstm.setInt(6, ad.getAdshowtime());
            pstm.setInt(7, ad.getAdnum());
            pstm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        close();
        System.out.println(sql);
    }

}
